Use RD4_DWCR
go

IF OBJECT_ID (N'dbo.ShowInfor', N'P') IS NOT NULL
DROP procedure ShowInfor;
go


create PROCEDURE ShowInfor
(@Branch VARCHAR(10)='ALL',
@InforType VARCHAR(10)='Salary'
)
as
	IF (@Branch = 'ALL' and @Infortype='Salary')
	Begin	
		SELECT ROW_NUMBER() OVER (
				ORDER BY [RateSalaryAnnual] DESC
				) AS [Row],
			NTILE(100) OVER (
				ORDER BY [RateSalaryAnnual] DESC
				) AS [ Rank],
			b.AlphaName,
			a.BUSINESSUNITCODE,
			a.JobType,
			Cast (RateSalaryAnnual as money),
			a.category01payroll,
			CONVERT(DATE, DateBirth) AS [DOB],
			CONVERT(DATE, [DateOriginalEmployment]) AS [Started date],
			CONVERT(DATE, [DateLastRaise]) AS [Date Last Raise],
			RatePreviousSalary
		FROM JDEPYEmployeeMasterInfo a,
			JDEABAddressBookMaster b
		WHERE b.[AddressNumber] = a.AddressNumber
			AND b.SearchTypeCode = 'E'
			--AND a.BusinessUnitCode = '         782'
		ORDER BY RateSalaryAnnual DESC
	END
	Else IF (@Branch <> 'IT' and @InforType='Salary')
	Begin
		SELECT ROW_NUMBER() OVER (
				ORDER BY [RateSalaryAnnual] DESC
				) AS [Row],
			NTILE(100) OVER (
				ORDER BY [RateSalaryAnnual] DESC
				) AS [ Rank],
			b.AlphaName,
			a.BUSINESSUNITCODE,
			a.JobType,
			RateSalaryAnnual,
			a.category01payroll,
			CONVERT(DATE, DateBirth) AS [DOB],
			CONVERT(DATE, [DateOriginalEmployment]) AS [Started date],
			CONVERT(DATE, [DateLastRaise]) AS [Date Last Raise],
			RatePreviousSalary
		FROM JDEPYEmployeeMasterInfo a,
			JDEABAddressBookMaster b
		WHERE b.[AddressNumber] = a.AddressNumber
			AND b.SearchTypeCode = 'E'
			AND a.BusinessUnitCode = '         782'
		ORDER BY RateSalaryAnnual DESC
	END
	ELSE IF (@InforType='BONUS' and @Branch='IT')
	BEGIN
		SELECT Cast (AmountGrossPay as money)as Bonus,	*
		FROM JDEPYEmployeeTransactionHist
		WHERE RemarkExplaination = 'Emp Inc Plan                  '
		and Fiscalyear='13'
		AND BusinessUnitCode = '         782'
	END
	Else IF (@InforType='BONUS' and @Branch='ALL')
	BEGIN
		SELECT Cast (AmountGrossPay as money)as Bonus,	CAST((SELECT SUM(AmountGrossPay) FROM 
		JDEPYEmployeeTransactionHist WHERE RemarkExplaination = 'Emp Inc Plan                  ' 
		and Fiscalyear='13' ) as money) as "TOTAL Bonus",*
		FROM JDEPYEmployeeTransactionHist
		WHERE RemarkExplaination = 'Emp Inc Plan                  '
		and Fiscalyear='13'
		ORDER BY AmountGrossPay DESC
	END


